package com.wgn.reportquery.dao;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.wgn.reportquery.cons.Const;
import com.wgn.reportquery.model.Query;
import com.wgn.reportquery.model.Reportdata;
import com.wgn.reportquery.model.User_SK;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class QueryDaoImple {
	Connection conn = null;
	static String queryDataType = "SELECT DISTINCT a.`INFO` FROM DATAS a where USERNAME='";

	public QueryDaoImple() {

		String driver = "com.mysql.jdbc.Driver";
		// String url =
		// "jdbc:mysql://127.0.0.1:3306/reportdatabase?characterEncoding=utf-8";

		try {
			Class.forName(driver); // classLoader,加载对应驱动
			conn = (Connection) DriverManager.getConnection(Const.MYSQLURL,
					Const.dbusername, Const.dbpassword);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	// public boolean exists(String sql) {
	// List<Reportdata> datas = new ArrayList<Reportdata>();
	// boolean exists = false;
	// PreparedStatement pstmt = null;
	// try {
	// pstmt = (PreparedStatement) conn.prepareStatement(sql);
	// ResultSet rs = pstmt.executeQuery();
	// while (rs.next()) {
	// exists = true;
	// break;
	//
	// }
	//
	// } catch (SQLException e) {
	// e.printStackTrace();
	// } finally {
	// try {
	// pstmt.close();
	// } catch (SQLException e) {
	// // TODO Auto-generated catch block
	// e.printStackTrace();
	// }
	// return exists;
	// }
	//
	// }

	public List<Reportdata> getAll(String sql) {
		List<Reportdata> datas = new ArrayList<Reportdata>();

		PreparedStatement pstmt;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				Reportdata r = new Reportdata(rs.getString(1), rs.getString(2),
						rs.getString(4), rs.getString(5), rs.getString(3),
						rs.getString(6), rs.getString(9), rs.getString(8));
				datas.add(r);

			}
			pstmt.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return datas;
	}

	public List<String> getAllDataType(String username) {
		List<String> list = new ArrayList();
		PreparedStatement pstmt;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(queryDataType
					+ username + "'");
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				String datatype = rs.getString(1);
				list.add(datatype);
			}
			pstmt.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	static String registSql = "insert into G_USER (USERNAME,PASSWD,SKUSERNAME,SKPASSWD) values(?,?,?,?)";
	static String registSql_card = "insert into CARD (CARDNO,USERNAME,CARDTYPE) values(?,?,?)";
	static String look4Sql = "SELECT DISTINCT a.`INFO` FROM DATAS a WHERE a.`INFO` LIKE '%##%' AND a.`USERNAME`=?";

	public List<String> look4Type(String userName, String typename) {
		List<String> bak = new ArrayList();
		PreparedStatement pstmt;
		try {

			pstmt = (PreparedStatement) conn.prepareStatement(look4Sql.replace(
					"##", typename));
			pstmt.setString(1, userName.trim());
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				String type = rs.getString(1);
				bak.add(type);
			}
			pstmt.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
		return bak;
	}

	// public boolean add(String userName, String passwd, String cards,
	// String sKUSERNAME, String sKPASSWD, String cardType)
	// throws SQLException {
	// boolean ok = false;
	//
	// PreparedStatement pstmt = (PreparedStatement) conn
	// .prepareStatement(registSql);
	// try {
	// conn.setAutoCommit(false); // 1.设置手动提交
	// // 插入用户表
	//
	// pstmt.setString(1, userName.trim());
	// pstmt.setString(2, passwd.trim());
	// pstmt.setString(3, sKUSERNAME.trim());
	// pstmt.setString(4, sKPASSWD.trim());
	//
	// pstmt.executeUpdate();
	// // 插入医保卡
	// pstmt = (PreparedStatement) conn.prepareStatement(registSql_card);
	// String[] cardTypeArray = cardType.split("#");
	// String[] cardArray = cards.split("#");
	// if (cards != null && cards.length() > 0) {
	//
	// for (int i = 0; i < cardArray.length; i++) {
	// if (cardArray[i].length() > 0) {
	// pstmt = (PreparedStatement) conn
	// .prepareStatement(registSql_card);
	// pstmt.setString(3, cardTypeArray[i]);
	// pstmt.setString(2, userName.trim());
	// pstmt.setString(1, cardArray[i]);
	//
	// pstmt.executeUpdate();
	//
	// }
	// }
	// }
	// conn.commit();
	// ok = true;
	//
	// conn.setAutoCommit(true); // 1.设置手动提交
	// } catch (SQLException e) {
	// e.printStackTrace();
	// } finally {
	// pstmt.close();
	// }
	// return ok;
	// }

	static String signSql = "select * from G_USER where USERNAME=? and PASSWD=?";

	public boolean sign(String userName, String passwd) {
		// TODO Auto-generated method stub
		boolean result = false;
		PreparedStatement pstmt;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(signSql);
			pstmt.setString(1, userName.trim());
			pstmt.setString(2, passwd.trim());
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				result = true;
				System.out.println("登陆成功");
			}
			pstmt.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return result;
	}

	final static String findcardssql = "SELECT CARD.`CARDNO` FROM CARD WHERE CARD.`USERNAME`=?";
	final static String findrjcardssql = "SELECT RJCARD.`CARDNO` FROM RJCARD WHERE RJCARD.`USERNAME`=?";
	final static String findhostpitalsql = "SELECT * FROM hostpitallist";

	public List<String> getCards(String username, String type) {
		// TODO Auto-generated method stub

		List<String> bak = new ArrayList();
		PreparedStatement pstmt;
		try {
			String sql = findcardssql;
			if (type.equalsIgnoreCase("rj"))
				sql = findrjcardssql;
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			pstmt.setString(1, username.trim());
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				String cardno = rs.getString(1);
				bak.add(cardno);
			}
			pstmt.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
		return bak;

	}

	public List<String> getHostPital() {

		// TODO Auto-generated method stub

		List<String> bak = new ArrayList();
		PreparedStatement pstmt;
		try {

			pstmt = (PreparedStatement) conn.prepareStatement(findhostpitalsql);

			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				String HOSPITALVALUE = rs.getString(1);
				String HOSPITALTITLE = rs.getString(2);
				if (isVIP(HOSPITALTITLE))
					HOSPITALTITLE = "<font  style='font-weight:bold;font-style:italic;' color='blue'>" + HOSPITALTITLE
							+ "</font>";
				bak.add("<td><input type='radio' name='hospital' value='##'>#@#</td>"
						.replace("##", HOSPITALVALUE).replace("#@#",
								HOSPITALTITLE));
			}
			pstmt.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
		return bak;

	}

	static List<String> list;

	private boolean isVIP(String input) {
		// TODO Auto-generated method stub
		if (null == list) {
			list = new ArrayList<String>();
			list.add("仁济");
			list.add("华山");
			list.add("曙光");
			list.add("中山");
			list.add("东方");
			list.add("瑞金");
		}
		for (String s : list) {
			if (input.indexOf(s) != -1)
				return true;
		}
		return false;
	}

	private final String getCardTypeSql = "SELECT `CARDTYPE` FROM CARD WHERE `CARDNO`=?;";
	private final String getHospitalNameSql = "SELECT hostpitallist.`HOSPITALTITLE` FROM hostpitallist WHERE hostpitallist.`HOSPITALVALUE`=?;";

	public void updateQuery(Query q) {
		// TODO Auto-generated method stub

		// TODO Auto-generated method stub

		PreparedStatement pstmt;
		try {

			pstmt = (PreparedStatement) conn.prepareStatement(getCardTypeSql);
			pstmt.setString(1, q.getCardId());
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				String cardType = rs.getString(1);
				q.setCardType(cardType);
			}
			pstmt = (PreparedStatement) conn
					.prepareStatement(getHospitalNameSql);
			pstmt.setString(1, q.getHospital_id());
			rs = pstmt.executeQuery();
			while (rs.next()) {
				String hospital_name = rs.getString(1);
				q.setHospital_name(hospital_name);
			}
			pstmt.close();

		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public User_SK getSkUser(String username, String passwd) {
		// TODO Auto-generated method stub

		// TODO Auto-generated method stub

		// TODO Auto-generated method stub
		User_SK user = new User_SK();
		PreparedStatement pstmt;
		try {

			pstmt = (PreparedStatement) conn
					.prepareStatement("SELECT SKUSERNAME,SKPASSWD FROM G_USER WHERE USERNAME=? AND PASSWD=?");
			pstmt.setString(1, username);
			pstmt.setString(2, passwd);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				String name = rs.getString(1);
				String passwd_sk = rs.getString(2);

				user.setName_sk(name);
				user.setPasswd_sk(passwd_sk);
			}

			pstmt.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
		return user;

	}

	public boolean register(String userName, String passwd) throws SQLException {

		boolean ok = false;

		PreparedStatement pstmt = (PreparedStatement) conn
				.prepareStatement(registSql);
		try {

			// 插入用户表

			pstmt.setString(1, userName.trim());
			pstmt.setString(2, passwd.trim());
			pstmt.setString(3, null);
			pstmt.setString(4, null);
			pstmt.executeUpdate();
			ok = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			pstmt.close();
		}
		return ok;

	}

	public boolean checkIfBindCards(String username) {
		// TODO Auto-generated method stub
		if (checkIfBindSkCards(username))
			return true;
		else
			return checkIfBindRjCards(username);

	}

	public boolean checkIfBindSkCards(String username) {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM CARD WHERE USERNAME=?";
		List<String> para = new ArrayList<String>();
		para.add(username);
		return ifExist(sql, para);
	}

	public boolean checkIfBindRjCards(String username) {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM RJCARD WHERE USERNAME=?";
		List<String> para = new ArrayList<String>();
		para.add(username);
		return ifExist(sql, para);
	}

	public boolean ifExist(String sql, List<String> para) {

		PreparedStatement pstmt = null;

		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			if (null != para) {

				for (int i = 0; i < para.size(); i++) {
					pstmt.setString(i + 1, para.get(i));
				}

			}
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return false;
	}

	public String getString(String sql, List<String> para) {

		PreparedStatement pstmt = null;

		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			if (null != para) {

				for (int i = 0; i < para.size(); i++) {
					pstmt.setString(i + 1, para.get(i));
				}

			}
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				String str = rs.getString(1);
				return str;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return null;
	}

	public void update(String sql, List<String> para) throws SQLException {
		// TODO Auto-generated method stub

		PreparedStatement pstmt = (PreparedStatement) conn
				.prepareStatement(sql);

		if (null != para) {

			for (int i = 0; i < para.size(); i++) {
				pstmt.setString(i + 1, para.get(i));
			}

		}

		pstmt.executeUpdate();

	}
}
